﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace DEMO.Y2015CS58.UI
{
    public class SinhVien_DAL
    {
        private DBAccess db;
        private int id;

        public SinhVien_DAL()
        {
            db = new DBAccess();
        }

        public DataTable SinhVien_GetAll()
        {
            return db.GetDataTable("SinhVIen_GetAll");
        }

        public bool SinhVien_Add(SinhVien_Info info)
        {
            //Goi store procedure SinhVien_Add

            //Khai baos mang cac tham so
            //C1            
            SqlParameter[] pr = new SqlParameter[6];
            pr[0] = new SqlParameter("Sinhvien_Ma", info.SinhVien_Ma);
            pr[1] = new SqlParameter("SinhVien_Ten", info.SinhVien_Ten);
            pr[2] = new SqlParameter("SinhVien_DiaChi", info.SinhVien_DiaChi);
            pr[3] = new SqlParameter("SinhVien_NgaySinh", info.SinhVien_NgaySinh);
            pr[4] = new SqlParameter("SinhVien_GioiTinh", info.SinhVien_GioiTinh);
            pr[5] = new SqlParameter("LopID", info.LopID);

            //C2
            SqlParameter[] pm = new SqlParameter[] 
            { 
                new SqlParameter("Sinhvien_Ma",info.SinhVien_Ma),
                new SqlParameter("SinhVien_Ten",info.SinhVien_Ten),
                new SqlParameter("SinhVien_DiaChi",info.SinhVien_DiaChi),
                new SqlParameter("SinhVien_NgaySinh",info.SinhVien_NgaySinh),
                new SqlParameter("SinhVien_GioiTinh",info.SinhVien_GioiTinh),
                new SqlParameter("LopID",info.LopID)
            };

            int i = db.ExecuteNonQuery("SinhVien_Add", pr);

            //if (i > 0)
            //    return true;
            //else
            //    return false;

            return i > 0 ? true : false;
        }
        public bool SinhVien_Update(SinhVien_Info info)
        {
            //Goi store procedure SinhVien_Add

            SqlParameter p = new SqlParameter();
            p.ParameterName = "Sinhvien_Ma";
            p.Value = info.SinhVien_Ma;

            SqlParameter[] pm = new SqlParameter[] 
            { 
                new SqlParameter("Sinhvien_Ma",info.SinhVien_Ma),
                new SqlParameter("SinhVien_Ten",info.SinhVien_Ten),
                new SqlParameter("SinhVien_DiaChi",info.SinhVien_DiaChi),
                new SqlParameter("SinhVien_NgaySinh",info.SinhVien_NgaySinh),
                new SqlParameter("SinhVien_GioiTinh",info.SinhVien_GioiTinh),
                new SqlParameter("LopID",info.LopID),
                new SqlParameter("SinhVien_ID",info.SinhVien_ID)
            };

            int i = db.ExecuteNonQuery("SinhVien_Add", pm);

            //if (i > 0)
            //    return true;
            //else
            //    return false;

            return i > 0 ? true : false;
        }

        public SinhVien_Info SinhVien_GetOne(int sinhVien_ID)
        {
            SinhVien_Info retVal = null;
            SqlDataReader dr = null;
            try
            {
                dr = db.ExecuteReader("SinhVien_GetOne", new SqlParameter[] { new SqlParameter("SinhVien_ID", sinhVien_ID) });
                while (dr.Read())
                {
                    retVal.LopID = Convert.ToInt32(dr["LopID"]);
                    retVal.SinhVien_DiaChi = dr["SinhVien_DiaChi"].ToString();
                    retVal.SinhVien_GioiTinh = Convert.ToBoolean(dr["SinhVien_GioiTinh"]);
                    retVal.SinhVien_ID = Convert.ToInt32(dr["SinhVien_ID"]);
                    retVal.SinhVien_Ma = dr["SinhVien_Ma"].ToString();
                    retVal.SinhVien_NgaySinh = Convert.ToDateTime(dr["SinhVien_NgaySinh"]);
                    retVal.SinhVien_Ten = dr["SinhVien_Ten"].ToString();
                }
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            finally
            {
                if (dr != null) dr.Close();
            }

            return retVal;
        }
        public bool SinhVien_Delte(int sinhVien_ID)
        {
            try
            {
                //C1
                //bool retVal = false;

                //if (db.ExecuteNonQuery("SinhVien_Delete", new SqlParameter[] { new SqlParameter("SinhVien_ID", sinhVien_ID) }) > 0)
                //    retVal = true;
                //return retVal;

                //C2

                return (db.ExecuteNonQuery("SinhVien_Delete", new SqlParameter[] { new SqlParameter("SinhVien_ID", sinhVien_ID) }) > 0);
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
        }
    }
}
